use Time::Local;
use HTTP::Date;
use strict;
use XML::LibXML;
use LWP::Simple;
use DBI;
use Config::IniFiles;

# This program read config_cdip.xml, get the spectral wave data file, parse it, then insert the data to the database. It will automatically update platform, sensor,sensor_type,obs_type,m_type,m_scalar_type,uom_type tables if no related record found. But it can't do the unit conversion yet.

#config
my $cfg=Config::IniFiles->new( -file => '/var/myscheduler/dbConfig.ini');
my $db_name="rcoos";
my $tname=$cfg->val('rcoos','tname');
my $db_user=$cfg->val('rcoos','username');
my $db_passwd=$cfg->val('rcoos','password');

my($temperature,$pressure,$timedate,$m_lon,$m_lat,$platform_handle,$organization_row_id, $platform_row_id,$counter,$airtemp,$airsolar,$airhumidity,$airpressure,$windspeed, $winddirection,$windgust,$rain,$metId);  

#for sqlite database
#my $timevalue="datetime('now')";
#my $dbname="waves.db";
#my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "",
#                    { RaiseError => 1, AutoCommit => 1 });
#if(!defined $dbh) {die "Cannot connect to database!\n";}

#for postgres database
#establish database connection
my $dbh = DBI->connect ( "dbi:Pg:dbname=$db_name", $db_user, $db_passwd);
if ( !defined $dbh ) {
       die "Cannot connect to database!\n";
       exit 0;
} 


my $timevalue="current_timestamp";

my $missing_value = '-99999';

##################
#read input files for looping through calls
##################

my $target_dir = '/tmp/response';

my $config_filepath = '/var/myscheduler/config_cdip_aux.xml';
my $config = XML::LibXML->new->parse_file($config_filepath);


foreach my $provider ($config->findnodes('//provider')) {
	my $data_url = $provider->findvalue('dataURL'); 
        my $provider_url=$provider->findvalue('url');
	my $startTime = $provider->findvalue('startTime'); 
 	my $endTime = $provider->findvalue('endTime');        
        my $organization=$provider->findvalue('name');
 	my $days = $provider->findvalue('days');         
	$metId = $provider->findvalue('metId');
	$platform_handle = $provider->findvalue('platformHandle');
	my $platform_Id = $provider->findvalue('platformId');
        my ($xml_url,$startcount);
  
	my $waverider_Id = $provider->findvalue('waveriderId');
        #=comment
        my $endTime = `date +%Y%m%d%H%M --date='+5 hours'`;
        chomp($endTime);
        my $startTime = `date +%Y%m%d%H%M --date='-2 hours'`;
        chomp($startTime);
        #=cut
        #$endTime="20081118";
        #$startTime="20081001";

        #$days=40;     
         # this is getting data from duck frf website
        if($days){
             $xml_url=$data_url."+".$days;
        }
        else {
              $xml_url=$data_url."+1";
        }
        
        
        #generate a random file handle concatenating a random number
        my $xml_filepath = "$target_dir/".$platform_Id."latestaux.txt";
        print $xml_url."\n";
        my $content = getstore($xml_url, $xml_filepath);
        die "Couldn't get $xml_url" unless defined $content; 
      
	my $sql = qq{ SELECT row_id from organization where short_name like '$organization' };
	#print $sql."\n";
	my $sth = $dbh->prepare( $sql );
	$sth->execute();
	($organization_row_id) = $sth->fetchrow_array;
        
	if ($organization_row_id) {} #print "$organization_name_lkp\n"; 
	else {
		print "organization not found - inserting\n";

		$sql = qq{ SELECT max(row_id) from organization  };
		#printsub getSensor{
  
                my ($m_type_id,$short_name)=@_; $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
		($organization_row_id) = $sth->fetchrow_array;	        
                $organization_row_id=$organization_row_id+1;
		$sql = qq{ INSERT into organization(row_id,row_entry_date,short_name,url) values ($organization_row_id,$timevalue,'$organization','$provider_url'); };
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
                $sth->finish();
         
	}

	##platform####################
	$sql = qq{ SELECT row_id,platform_handle,fixed_latitude,fixed_longitude from platform where organization_id = $organization_row_id and short_name = '$platform_Id' };
	#print $sql."\n";
	$sth = $dbh->prepare( $sql );
	$sth->execute();
	($platform_row_id,$platform_handle,$m_lat,$m_lon) = $sth->fetchrow_array;

	if ($platform_row_id) {
            
            ;
        } #print "$platform_name_lkp\n"; }
	else {
		print "platform not found - inserting\n";

		#requery for new row_id
		$sql = qq{ SELECT max(row_id) from platform  };
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
		($platform_row_id) = $sth->fetchrow_array;
                $platform_row_id++;
                my $undefined_platform_type_id=6;
                #$platform_desc=~ s/,//g;
                my $platform_url=$provider_url."/station_page.php?station=$platform_Id";
		$sql = "INSERT into platform(row_id,row_entry_date,organization_id,type_id,short_name,platform_handle) values ($platform_row_id,$timevalue,$organization_row_id,$undefined_platform_type_id,'$platform_Id','$platform_handle')";
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
                $sth->finish();
        }

        #get row_id from multi_obs table, should set the multi_obs table row_id autoincrement
        
        if($counter==0){
                      $sql="select max(row_id) from $tname";
                      $sth = $dbh->prepare($sql);
                      $sth->execute();
                      ($counter) = $sth->fetchrow_array;
                      $startcount=$counter;
                      $counter++;
        }  

        print "counter is $counter\n";
       
        open(FILE,"<$xml_filepath");
        my @data=<FILE>;
        close(FILE);
  
       #parse the file
       for(my $i=0;$i<@data;$i++){
           $data[$i]=~ s/\n//g;
           $data[$i]=~ s/<br>//g;
           my @temp=split(/ /,$data[$i]);
           my @temp1;
           for(my $n=0;$n<@temp;$n++){
                      if($temp[$n] ne ""){
                         push(@temp1,$temp[$n]);
                      }
           }
           my $c=@temp1;

           if($metId==3){
              $windspeed="null";$winddirection="null";$windgust="null";
              if($c<9){next;}
              if($temp1[0]>2005){
                 if($temp1[1]<10){$temp1[1]="0".$temp1[1];}
                 if($temp1[2]<10){$temp1[2]="0".$temp1[2];}
                 if($temp1[3]<10){$temp1[3]="0".$temp1[3];}
                 if($temp1[4]<10){$temp1[4]="0".$temp1[4];}
                 $timedate=$temp1[0]."-".$temp1[1]."-".$temp1[2]." ".$temp1[3].":".$temp1[4];
                 $windspeed=$temp1[9];$windgust=$temp1[8];$winddirection=$temp1[10];
       #          print "wind speed=$windspeed; wingust=$windgust;winddirection=$winddirection\n";
                 insert();
              }
           }
           elsif($metId==2){
              $airtemp="null";$airpressure="null";$airhumidity="null";
              if($c<9){next;}
              if($temp1[0]>2005){
                 if($temp1[1]<10){$temp1[1]="0".$temp1[1];}
                 if($temp1[2]<10){$temp1[2]="0".$temp1[2];}
                 if($temp1[3]<10){$temp1[3]="0".$temp1[3];}
                 if($temp1[4]<10){$temp1[4]="0".$temp1[4];}
                 $timedate=$temp1[0]."-".$temp1[1]."-".$temp1[2]." ".$temp1[3].":".$temp1[4];
                 $airtemp=$temp1[5];$airpressure=$temp1[6];$airhumidity=$temp1[7];
                 insert();
              }
           }
           elsif($metId==4){
              $airpressure="null";$windspeed="null";$winddirection="null";$windgust="null";
              $airtemp="null";$airsolar="null";$airhumidity="null";$rain="null";
              if($c<20){next;}
              if($temp1[0]>2005){
                 if($temp1[1]<10){$temp1[1]="0".$temp1[1];}
                 if($temp1[2]<10){$temp1[2]="0".$temp1[2];}
                 if($temp1[3]<10){$temp1[3]="0".$temp1[3];}
                 if($temp1[4]<10){$temp1[4]="0".$temp1[4];}
                 $timedate=$temp1[0]."-".$temp1[1]."-".$temp1[2]." ".$temp1[3].":".$temp1[4];
                 $timedate=get_gmt_time($timedate);
                  $windspeed=$temp1[9];$winddirection=$temp1[10];$windgust=$temp1[8];$airtemp=$temp1[16];$airpressure=$temp1[17];$airhumidity=$temp1[18];
                 insert();
              }

           } 
           elsif($metId==1){
              $airtemp="null";$airsolar="null";$airhumidity="null";$rain="null";
              $airpressure="null";$windspeed="null";$winddirection="null";$windgust="null";
              if($c<16){next;}
              if($temp1[0]>2005){
                if($temp1[1]<10){
                     $temp1[1]="0".$temp1[1];
                }
                if($temp1[2]<10){
                     $temp1[2]="0".$temp1[2];
                }
                $timedate=$temp1[0]."-".$temp1[1]."-".$temp1[2]." ".substr($temp1[3],0,2).":".substr($temp1[3],2,2);
                $timedate=get_gmt_time($timedate,4);
                $airtemp=$temp1[4];
                $airhumidity=$temp1[5];
                $airpressure=$temp1[8];
                $airsolar=$temp1[9];
                $windspeed=$temp1[10];
                $winddirection=$temp1[12];
                $windgust=$temp1[13];
                $rain=$temp1[16];
                insert();
                 #print "$timedate \t $airtemp\n";
              } 
                         
            }
          else {
                 $pressure="null";   $temperature="null";

               if($c<14){next;}
               if($temp1[0]>2005){
                $timedate=$temp1[0]."-".$temp1[1]."-".$temp1[2]." ".$temp1[3].":".$temp1[4];
                $pressure=$temp1[12];
                $temperature=$temp1[13];
                insert();
              } 
               
         } # end of metId 

        } #end of data line
} #foreach provider


 $dbh->disconnect();

exit 0;

##############################################

sub escape_literals {

my $platform_url = shift;
$platform_url =~ s/&/&amp;/ ;

return $platform_url;

}


sub getMtype{
     my ($obs_type,$uom_type)=@_;
     my $sql="select m_type.row_id,uom_type.display from m_type 
              left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id
              left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
              left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
              where obs_type.standard_name='$obs_type'";
               # print "\n"."$sql\n";
     my $sth = $dbh->prepare($sql);
        $sth->execute();
     my ($m_type_id,$uom_name) = $sth->fetchrow_array;
     $sth->finish();

     if(!$m_type_id){

         #updtae obs_type table
         $sql="select row_id from obs_type where standard_name='$obs_type'";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($obstype_id) = $sth->fetchrow_array;
         if(!$obstype_id){
           $sql="select max(row_id) from obs_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($obstype_id) = $sth->fetchrow_array;
           $obstype_id++;
           $sql="insert into obs_type(row_id,standard_name) values($obstype_id,'$obs_type')";
           $sth = $dbh->prepare($sql);
           print "$sql\n";
           $sth->execute();
           $sth->finish();
          }
           #updtae uom_type table
           $sql="select row_id from uom_type where display='$uom_type'";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($uom_id) = $sth->fetchrow_array;
           if(!$uom_id){
             $sql="select max(row_id) from uom_type";
             $sth = $dbh->prepare($sql);
             $sth->execute();
             my ($uom_id) = $sth->fetchrow_array;
             $uom_id++;
             $sql="insert into uom_type(row_id,standard_name,display) values($uom_id,'$uom_type','$uom_type')";
             $sth = $dbh->prepare($sql);
             print "$sql\n";
             $sth->execute();
             $sth->finish();
            }

        #updtae m_scalar_type table
         $sql="select row_id from m_scalar_type where obs_type_id=$obstype_id and uom_type_id=$uom_id";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($mscalar_id) = $sth->fetchrow_array;
         if(!$mscalar_id){
           $sql="select max(row_id) from m_scalar_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($mscalar_id) = $sth->fetchrow_array;
           $mscalar_id++;
           $sql="insert into m_scalar_type(row_id,obs_type_id,uom_type_id) values($mscalar_id,$obstype_id,$uom_id)";
           print "$sql\n";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           $sth->finish();
          }

                  #update mtype table   
          $sql="select max(row_id) from m_type";
          $sth = $dbh->prepare($sql);
          $sth->execute();
          my ($m_type_id) = $sth->fetchrow_array;
          $m_type_id++;
          $sql="insert into m_type(row_id,num_types,description,m_scalar_type_id) values($m_type_id,1,'$obs_type',$mscalar_id)";
          #print "$sql\n";
          $sth = $dbh->prepare($sql);
          $sth->execute();
          $sth->finish();

     } #end of m_type_id

     return $m_type_id;
}

sub getSensor{
  
    my ($m_type_id,$short_name)=@_;
    my $sql="select row_id from sensor where platform_id=$platform_row_id and m_type_id=$m_type_id";
    
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    # print $sql."\n";
    my ($sensor_id) = $sth->fetchrow_array;
    $sth->finish();
    if(!$sensor_id){
         #insert new record to sensor table
         $sql="select row_id from sensor_type where type_name='$short_name'";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($type_id) = $sth->fetchrow_array;
         $sth->finish();
         if(!$type_id){
           $sql="select max(row_id) from sensor_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($type_id) = $sth->fetchrow_array;
           $type_id++;
           $sql="insert into sensor_type(row_id,type_name,description) values($type_id,'$short_name','$short_name')";
           $sth = $dbh->prepare($sql);
           #print "$sql\n";
           $sth->execute();
           $sth->finish();
         }
         $sql="select max(row_id) from sensor";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         ($sensor_id) = $sth->fetchrow_array;
         $sensor_id++;
         $sql="insert into sensor(row_id,row_entry_date,platform_id,type_id,m_type_id,short_name) values ($sensor_id,$timevalue,$platform_row_id,$type_id,$m_type_id,'$short_name')";
          $sth = $dbh->prepare($sql);
          #print "inserting to sensor table $sql\n";
          $sth->execute();
          $sth->finish();
    }
    return $sensor_id;
}


sub insert{
   
   my (@obstype_array,@uom_array,@obs_array);
   if($metId==3){
     @obstype_array=("wind_speed","wind_from_direction","wind_gust");
     @uom_array=("m_s-1","degrees_true","m_s-1");
     @obs_array=($windspeed,$winddirection,$windgust);
   }
   elsif($metId==2){
     @obstype_array=("air_temperature","relative_humidity","air_pressure");
     @uom_array=("celsius","percent","mb");
     @obs_array=($airtemp,$airhumidity,$airpressure);
   }
   elsif($metId==4){
      
     @obstype_array=("air_temperature","relative_humidity","air_pressure","wind_speed", "wind_from_direction","wind_gust");
     @uom_array=("celsius","percent","mb","m_s-1","degrees_true","m_s-1");
     @obs_array=($airtemp,$airhumidity,$airpressure,$windspeed,$winddirection,$windgust);
   }
   elsif($metId==1){
     @obstype_array=("air_temperature","relative_humidity","air_pressure","solar_radiation","wind_speed", "wind_from_direction","wind_gust","precipitation_accumulated_daily");
     @uom_array=("celsius","percent","mb","millimoles_per_m^2","m_s-1","degrees_true","m_s-1","millimeter");
     @obs_array=($airtemp,$airhumidity,$airpressure,$airsolar,$windspeed,$winddirection,$windgust,$rain);
   }
   else{
     @obstype_array=("water_temperature","water_pressure");
     @uom_array=("celsius","m");
     @obs_array=($temperature,$pressure);
   }
for (my $m=0;$m<@obstype_array;$m++){
   # "\n$obstype_array[$m]\n\n";
   my $m_type_id=getMtype($obstype_array[$m],$uom_array[$m]);
   my $sensor_id=getSensor($m_type_id,$obstype_array[$m]);
        
   my $sql="select row_id from $tname where m_date='$timedate' and sensor_id=$sensor_id and m_type_id=$m_type_id";
   
   my $sth = $dbh->prepare($sql);
   $sth->execute();
   my ($row_id) = $sth->fetchrow_array;
   $sth->finish();

   if($row_id>0){;}
   else {
                        
        my $qc_level="null";
        if(($sensor_id==7077)||($sensor_id==7078)||($sensor_id==7079)||($sensor_id==7069)||($sensor_id==7070)||($sensor_id==7071)||($sensor_id==7085)||($sensor_id==7086)||($sensor_id==7087)){$qc_level=1;}        
        $sql="INSERT into $tname (row_id,row_entry_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value,qc_level) values ($counter,$timevalue,'$platform_handle',$sensor_id,$m_type_id,'$timedate',$m_lon,$m_lat,$missing_value,$obs_array[$m],$qc_level)";
              
       #if($obstype_array[$m] eq "wind_speed"){ 
       # print $sql."\n"; 
      #}
             $sth = $dbh->prepare($sql);         
             $sth->execute();
             $sth->finish();
             $counter++;
    }

}
   
}

sub get_gmt_time{
 my ($dat,$dsttime)=@_;
 
 my $t=str2time($dat);
 my ($sec,$min,$hour,$mday,$mon,$year,$twday,$yday,$isdst) = localtime($t);
 #my ($sec,$min,$hour,$mday,$mon,$year,$twday,$yday,$isdst) = localtime($t+$dsttime*3600);
     $year=$year+1900;
     $mon=$mon+1;
     if($mon<10){
        $mon="0".$mon;
     }
    if($mday<10){
       $mday="0".$mday;
    }
    if($sec<10){
       $sec="0".$sec;
    }
   if($min<10){
     $min="0".$min;
   }
   if($hour<10){
     $hour="0".$hour;
   }
   $dat=$year."-".$mon."-".$mday." ".$hour.":".$min.":".$sec;
   
   return $dat;
}


